﻿#include "precomp.h"
#include "jmyexcel.h"
#include "jexcel.h"
#include "jtableview.h"
#include "jsqltablemgr.h"
#include "jsqldatamgr.h"

// - class JMyExcelPrivate -

class JMyExcelPrivate
{
    J_DECLARE_PUBLIC(JMyExcel)
    public:
        JMyExcelPrivate(JMyExcel *parent = 0) :
      q_ptr(parent),
        jexcel(0),
        columnCount(0)
    {

    }

    ~JMyExcelPrivate()
    {
        jexcel->deleteLater();
        jexcel = 0;
    }

    void init();

    // 正整数到编号转换
    static std::string itos26(unsigned int n);

    // 字符串到数字的转换
    static unsigned int stoi26(const std::string &s);

    //
    QString currentColumnEnd() const;

private:
    JExcel *jexcel;
    int columnCount;
};

void JMyExcelPrivate::init()
{
    Q_Q(JMyExcel);

    jexcel = new JExcel(q);
}

std::string JMyExcelPrivate::itos26(unsigned int n)
{
    std::string s;
    unsigned int m = 0;

    while(n >= 1) {
        m = n % 26;
        if (m != 0) {
            s.insert(s.begin(), 'A' + m - 1);
        } else {
            s.insert(s.begin(), 'Z');
            n--;
        }

        n /= 26;
    }

    return s;
}

unsigned int JMyExcelPrivate::stoi26(const std::string &s)
{
    unsigned int result = 0, m = 0, n = 0;
    std::string::const_reverse_iterator citer = s.rbegin();
    for (; citer != s.rend(); ++citer) {
        n = citer - s.rbegin() + 1;
        m = 1;
        while(n-- > 1) {
            m *= 26;
        }
        result += m * (*citer - 'A' + 1);
    }

    return result;
}

QString JMyExcelPrivate::currentColumnEnd() const
{
    return QString::fromStdString(itos26(columnCount));
}

// - class JMyExcel -

J_IMPLEMENT_SINGLE_INSTANCE(JMyExcel)

JMyExcel::JMyExcel(QObject *parent) :
    QObject(parent),
    d_ptr(new JMyExcelPrivate(this))
{
    Q_D(JMyExcel);
    d->init();
}

JMyExcel::~JMyExcel()
{
    Q_D(JMyExcel);
    delete d;
}

bool JMyExcel::setTitle(const JFileNameInfo &fileNameInfo, int columnCount)
{
    Q_UNUSED(fileNameInfo);
    QString title = QStringLiteral("数据查询结果清单");
    return setSheetTitle(1, title, qMax(10, columnCount));
}

bool JMyExcel::setSheetTitle(int index, const QString &text, int columnCount)
{
    Q_D(JMyExcel);

    //
    d->columnCount = columnCount;

    //
    QAxObject *sheet = d->jexcel->sheet(index);
    if (sheet == 0) {
        return false;
    }

    //
    QAxObject *rangeTitle =
            sheet->querySubObject("Range(QVariant)", QString("A1:%1")
                                  .arg(d->currentColumnEnd()) + '2');
    if (rangeTitle == 0) {
        return false;
    }

    //
    rangeTitle->setProperty("HorizontalAlignment", -4108);
    rangeTitle->setProperty("VerticalAlignment", -4108);
    rangeTitle->setProperty("WrapText", true);
    rangeTitle->setProperty("MergeCells", true);
    rangeTitle->setProperty("Value", text);

    // 设置合并后的单元格边框、文本颜色和字体
    QAxObject *interior = rangeTitle->querySubObject("Interior");
    if (interior) {
        //interior->setProperty("Color", QColor(0, 64, 255));
    }
    QAxObject *cellFont = rangeTitle->querySubObject("Font");
    if (cellFont) {
        cellFont->setProperty("Bold", true);
        cellFont->setProperty("Size", 18);
        cellFont->setProperty("Color", QColor(64, 64, 32));
        cellFont->setProperty("Name", "Courier New");
    }
    //
    QAxObject *borders = rangeTitle->querySubObject("Borders");
    if (borders) {
        //borders->setProperty("Color", QColor(0, 64, 220));
    }

    return true;
}

bool JMyExcel::setSiftArea(const JTableView *tableView)
{
    if (tableView == 0) {
        return false;
    }

    Q_D(JMyExcel);
    QAxObject *sheet1 = d->jexcel->sheet(1);
    if (sheet1 == 0) {
        return false;
    }

    //
    sheet1->setProperty("Name", QStringLiteral("热点范围约束条件"));

    ////////////////////////////////////////////////////////////////////

    //
    QAxObject *rangeTitle = sheet1->querySubObject("Range(QVariant)", QString("A4:%1")
                                                   .arg(d->currentColumnEnd() + '4'));
    if (rangeTitle == 0) {
        return false;
    }

    //
    rangeTitle->setProperty("HorizontalAlignment", -4108);
    rangeTitle->setProperty("VerticalAlignment", -4108);
    rangeTitle->setProperty("WrapText", true);
    rangeTitle->setProperty("MergeCells", true);
    rangeTitle->setProperty("Value", tableView->windowTitle());

    // 设置合并后的单元格边框、文本颜色和字体
    QAxObject *interior = rangeTitle->querySubObject("Interior");
    if (interior) {
        //interior->setProperty("Color", QColor(0, 64, 255));
    }
    QAxObject *cellFont = rangeTitle->querySubObject("Font");
    if (cellFont) {
        cellFont->setProperty("Bold", true);
        cellFont->setProperty("Size", 14);
        cellFont->setProperty("Color", QColor(32, 32, 32));
        cellFont->setProperty("Name", "Courier New");
    }

    ////////////////////////////////////////////////////////////////////

    //
    const int columnCount = tableView->columnCount();
    QString sRangeColumnEnd = QString::fromStdString(d->itos26(columnCount));
    //
    QAxObject *rangeSiftAreaHeader =
            sheet1->querySubObject("Range(QVariant)", QString("A5:%1")
                                   .arg(sRangeColumnEnd + '5'));
    if (rangeSiftAreaHeader == 0) {
        return false;
    }
    //
    QList<QVariant> headerLabels;
    for (int col = 0; col < columnCount; ++col) {
        QStandardItem *item = tableView->horizontalHeaderItem(col);
        if (item == 0) {
            continue;
        }
        headerLabels.append(item->data(Qt::DisplayRole));
    }
    rangeSiftAreaHeader->setProperty("Value", headerLabels);

    ////////////////////////////////////////////////////////////////////

    //
    const int rowCount = qMin(1, tableView->rowCount());    // 只允许一行
    //
    QAxObject *rangeSiftArea =
            sheet1->querySubObject("Range(QVariant)", QString("A6:%1")
                                   .arg(sRangeColumnEnd + QString::number(6 + rowCount)));
    if (rangeSiftArea == 0) {
        return false;
    }
    //
    QList<QVariant> siftAreas;
    for (int row = 0; row < rowCount; ++row) {
        QVariantList vl;
        // 水平偏差
        vl.append(tableView->item(row, 0)->data(Qt::DisplayRole));
        // 基准坐标
        vl.append(tableView->item(row, 1)->data(Qt::DisplayRole));
        // 垂直偏差
        vl.append(tableView->item(row, 2)->data(Qt::DisplayRole));
        //
        siftAreas.append(QVariant(vl));
    }
    //
    rangeSiftArea->setProperty("Value", QVariant(siftAreas));

    ////////////////////////////////////////////////////////////////////

    return true;
}

bool JMyExcel::setSiftRestrain(const JTableView *tableView)
{
    if (tableView == 0) {
        return false;
    }

    Q_D(JMyExcel);
    QAxObject *sheet1 = d->jexcel->sheet(1);
    if (sheet1 == 0) {
        return false;
    }

    ////////////////////////////////////////////////////////////////////

    //
    QAxObject *rangeTitle = sheet1->querySubObject("Range(QVariant)", QString("A7:%1")
                                                   .arg(d->currentColumnEnd() + '7'));
    if (rangeTitle == 0) {
        return false;
    }

    //
    rangeTitle->setProperty("HorizontalAlignment", -4108);
    rangeTitle->setProperty("VerticalAlignment", -4108);
    rangeTitle->setProperty("WrapText", true);
    rangeTitle->setProperty("MergeCells", true);
    rangeTitle->setProperty("Value", tableView->windowTitle());

    // 设置合并后的单元格边框、文本颜色和字体
    QAxObject *interior = rangeTitle->querySubObject("Interior");
    if (interior) {
        //interior->setProperty("Color", QColor(0, 64, 255));
    }
    QAxObject *cellFont = rangeTitle->querySubObject("Font");
    if (cellFont) {
        cellFont->setProperty("Bold", true);
        cellFont->setProperty("Size", 14);
        cellFont->setProperty("Color", QColor(32, 32, 32));
        cellFont->setProperty("Name", "Courier New");
    }

    ////////////////////////////////////////////////////////////////////

    const int columnCount = tableView->columnCount();
    QString sRangeColumnEnd = QString::fromStdString(d->itos26(columnCount));
    //
    QAxObject *rangeSiftRestrainHeader =
            sheet1->querySubObject("Range(QVariant)", QString("A8:%1")
                                   .arg(sRangeColumnEnd + '8'));
    if (rangeSiftRestrainHeader == 0) {
        return false;
    }
    //
    QList<QVariant> headerLabels;
    for (int col = 0; col < columnCount; ++col) {
        QStandardItem *item = tableView->horizontalHeaderItem(col);
        if (item == 0) {
            continue;
        }
        headerLabels.append(item->data(Qt::DisplayRole));
    }
    rangeSiftRestrainHeader->setProperty("Value", headerLabels);

    ////////////////////////////////////////////////////////////////////

    //
    const int rowCount = tableView->rowCount();
    //
    QAxObject *rangeSiftRestrain =
            sheet1->querySubObject("Range(QVariant)", QString("A9:%1")
                                   .arg(sRangeColumnEnd + QString::number(8 + rowCount)));
    if (rangeSiftRestrain == 0) {
        return false;
    }
    //
    QList<QVariant> siftAreas;
    for (int row = 0; row < rowCount; ++row) {
        QVariantList vl;
        // 信号名
        vl.append(tableView->item(row, 0)->data(Qt::DisplayRole));
        // 基准值
        vl.append(tableView->item(row, 1)->data(Qt::DisplayRole));
        // 偏差值
        vl.append(tableView->item(row, 2)->data(Qt::DisplayRole));
        //
        siftAreas.append(QVariant(vl));
    }
    //
    rangeSiftRestrain->setProperty("Value", QVariant(siftAreas));

    ////////////////////////////////////////////////////////////////////

    return true;
}

bool JMyExcel::setRecordData(const QTabWidget *tabWidget)
{
    if (tabWidget == 0) {
        return false;
    }

    Q_D(JMyExcel);
    QString title = QStringLiteral("数据查询结果清单");
    const int tabCount = tabWidget->count();
    for (int i = 0; i < tabCount; ++i) {
        //
        const QWidget *liteTableWidget = tabWidget->widget(i);
        if (liteTableWidget == 0) {
            continue;       // error
        }
        //
        const JTableView *tableView = liteTableWidget->findChild<const JTableView *>("tableView");
        if (tableView == 0) {
            continue;       // error
        }
        //
        QAxObject *sheet = d->jexcel->appendSheet(tabWidget->tabText(i));
        if (sheet == 0) {
            continue;       // error
        }

        //
        setSheetTitle(2 + i, title, qMax(10, tableView->columnCount()));

        ////////////////////////////////////////////////////////////////////

        //
        QString sRangeColumnEnd = d->currentColumnEnd();

        //
        QAxObject *rangeSiftTable = sheet->querySubObject("Range(QVariant)", QString("A4:%1%2")
                                                          .arg(sRangeColumnEnd)
                                                          .arg(4 + tableView->rowCount() + 1));
        if (rangeSiftTable == 0) {
            continue;       // error
        }
        //
        QAxObject *borders = rangeSiftTable->querySubObject("Borders");
        if (borders) {
            borders->setProperty("Weight", 2);
        }

        ////////////////////////////////////////////////////////////////////

        //
        QAxObject *rangeTitle = sheet->querySubObject("Range(QVariant)", QString("A4:%1")
                                                      .arg(sRangeColumnEnd + '4'));
        if (rangeTitle == 0) {
            continue;       // error
        }

        //
        rangeTitle->setProperty("HorizontalAlignment", -4108);
        rangeTitle->setProperty("VerticalAlignment", -4108);
        rangeTitle->setProperty("WrapText", true);
        rangeTitle->setProperty("MergeCells", true);
        rangeTitle->setProperty("Value", QStringLiteral("筛选结果数据"));

        // 设置合并后的单元格边框、文本颜色和字体
        QAxObject *interior = rangeTitle->querySubObject("Interior");
        if (interior) {
            //interior->setProperty("Color", QColor(0, 64, 255));
        }
        QAxObject *cellFont = rangeTitle->querySubObject("Font");
        if (cellFont) {
            cellFont->setProperty("Bold", true);
            cellFont->setProperty("Size", 14);
            cellFont->setProperty("Color", QColor(32, 32, 32));
            cellFont->setProperty("Name", "Courier New");
        }

        ////////////////////////////////////////////////////////////////////

        const int columnCount = tableView->columnCount();
        sRangeColumnEnd = QString::fromStdString(d->itos26(columnCount));
        //
        QAxObject *rangeHeader =
                sheet->querySubObject("Range(QVariant)", QString("A5:%1")
                                      .arg(sRangeColumnEnd + '5'));
        if (rangeHeader == 0) {
            return false;
        }
        //
        QList<QVariant> headerLabels;
        for (int col = 0; col < columnCount; ++col) {
            QStandardItem *item = tableView->horizontalHeaderItem(col);
            if (item == 0) {
                continue;
            }
            headerLabels.append(item->data(Qt::DisplayRole));
        }
        rangeHeader->setProperty("Value", headerLabels);

        ////////////////////////////////////////////////////////////////////

        // 读取处于热点区域的数据点
        int pageRowCount = 50000;
        int totalRowCount = liteTableWidget->property("totalRowCount").toInt();
        int pageCount = totalRowCount / pageRowCount + ((totalRowCount % pageRowCount) ? 1 : 0);
        int rowIndex = 6;
        QString sRange;
        QAxObject *rangeRecord = 0;
        QList<QVariant> data;
        //
        JSqlDataMgr::instance()->transaction();
        //
        QString DYXHB = liteTableWidget->windowTitle();
        QString DYZB = tableView->windowTitle();

        // 读取对应信号表数据
        QStringList columnSymbols = JSqlDataMgr::instance()->readSignalTableColumnSymbol(DYXHB);
        if (columnSymbols.isEmpty()) {
            return false;
        }
        //
        for (int page = 0; page < pageCount; ++page) {
            //
            if (!JSqlDataMgr::instance()->readRecordDataV(DYZB,
                                                          liteTableWidget->property("machSection").toString(),
                                                          liteTableWidget->property("heightSection").toString(),
                                                          liteTableWidget->property("range").toRectF(),
                                                          columnSymbols,
                                                          data,
                                                          page * pageRowCount,
                                                          pageRowCount,
                                                          columnSymbols.first())) {
                JSqlDataMgr::instance()->rollback();
                continue;       // 读取失败
            }
            //
            sRange = QString("A%1:%2").arg(rowIndex)
                    .arg(sRangeColumnEnd + QString::number(rowIndex - 1 + data.count()));
            //
            rangeRecord = sheet->querySubObject("Range(QVariant)", sRange);
            if (rangeRecord == 0) {
                continue;       // 获取失败
            }
            //
            rangeRecord->setProperty("Value", QVariant(data));
            //
            rowIndex += data.count();
            data.clear();
        }
        //
        JSqlDataMgr::instance()->commit();

        ////////////////////////////////////////////////////////////////////
    }

    return true;
}

bool JMyExcel::saveBaseLineToSql(const QString &filePath)
{
    QList<QVector<QPointF> > groupsData;
    //
    if (!readBaseLine(filePath, groupsData)) {
        return false;
    }

    //
    if (groupsData.isEmpty()) {
        return true;
    }

    if (JSqlTableMgr::instance()->existsTable(JSqlTableMgr::baseLineTableName())) {
        JSqlTableMgr::instance()->clearTable(JSqlTableMgr::baseLineTableName());
    } else if (!JSqlTableMgr::instance()->createBaseLineTable()) {
        return false;
    }

    //
    if (!JSqlDataMgr::instance()->addBaseLine(groupsData)) {
        return false;
    }

    return true;
}

bool JMyExcel::readBaseLine(const QString &filePath, QList<QVector<QPointF> > &groupsData)
{
    Q_D(JMyExcel);

    // 初始化输出参数
    groupsData.clear();

    //
    if (!d->jexcel->open(filePath, QIODevice::ReadOnly)) {
        qCritical() << QStringLiteral("%1 open failure!").arg(filePath);
        return false;
    }

    //
    QAxObject *sheet = d->jexcel->sheet(1);
    if (sheet == 0) {
        return false;       // has no data
    }

    //
    QAxObject *usedRange = sheet->querySubObject("UsedRange");
    QAxObject *rows = usedRange->querySubObject("Rows");
    QAxObject *columns = usedRange->querySubObject("Columns");
    int startRow = usedRange->property("Row").toInt();          // 获取起始行
    int startColumn = usedRange->property("Column").toInt();    // 获取起始列
    int rowCount = rows->property("Count").toInt();             // 获取行数
    int columnCount = columns->property("Count").toInt();       // 获取列数

    //
    QString sRange = QString("%1%2:%3%4")
            .arg(QString::fromStdString(d->itos26(startColumn)))
            .arg(startRow)
            .arg(QString::fromStdString(d->itos26(startColumn + columnCount - 1)))
            .arg(startRow + rowCount - 1);

    //
    QAxObject *rangeLines = sheet->querySubObject("Range(QVariant)", sRange);
    if (rangeLines == 0) {
        return false;       // 获取失败
    }

    //
    QVariant varData = rangeLines->property("Value");
    if (varData.isNull() || varData.type() != QVariant::List) {
        return false;       // 数据无效
    }

    //
    QListIterator<QVariant> citerData(varData.toList());
    while (citerData.hasNext()) {
        // M
        QListIterator<QVariant> citerRowM(citerData.next().toList());
        if (citerRowM.hasNext()) {
            if (citerRowM.peekNext().isNull()) {
                continue;   // 无效行
            }
        }
        // H
        QListIterator<QVariant> citerRowH(citerData.next().toList());
        if (citerRowH.hasNext()) {
            if (citerRowH.peekNext().isNull()) {
                continue;   // 无效行
            }
        }
        // 跳过第一列
        if (citerRowM.hasNext() && citerRowH.hasNext()) {
            citerRowM.next();
            citerRowH.next();
        }
        //
        QVector<QPointF> points;
        //
        while (citerRowM.hasNext() && citerRowH.hasNext()) {
            // M
            const QVariant &dataM = citerRowM.next();
            if (dataM.isNull()) {
                break;      // 后续数据无效
            }
            // H
            const QVariant &dataH = citerRowH.next();
            //
            points.append(QPointF(dataM.toDouble(), dataH.toDouble()));
        }
        //
        if (!points.isEmpty()) {
            groupsData.append(points);
        }
    }

    //
    d->jexcel->close();

    return true;
}

bool JMyExcel::readBaseLine(QList<QVector<QPointF> > &groupsData)
{
    return JSqlDataMgr::instance()->readBaseLine(groupsData);
}

bool JMyExcel::create()
{
    Q_D(JMyExcel);
    bool result = d->jexcel->create();
    if (!result) {
        return false;
    }

    d->jexcel->clearSheet();

    return true;
}

bool JMyExcel::saveAs(const QString &filePath)
{
    Q_D(JMyExcel);
    return d->jexcel->saveAs(filePath);
}

bool JMyExcel::close()
{
    Q_D(JMyExcel);
    return d->jexcel->close();
}

bool JMyExcel::show(bool enabled)
{
    Q_D(JMyExcel);
    return d->jexcel->show(enabled);
}
